﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlServerCe;
using System.IO;
using Less;
using Less.Tools;
using PetaPoco;
using Less.Core;

/// <summary>
/// Encapsulates the LessFramework database access.
/// Logging, configuration, security....
/// </summary>
public static class Data
{
   
    private static bool? _prepared = false; // null= working, false= notPrepared, true = alreadyPrepared
    private static readonly object Token = new object();

    /// <summary>
    /// Name of the connectionstring to use
    /// </summary>
    public static string LessConnectionStringName
    {
        get
        {
            string name = ConfigurationManager.AppSettings["Data.LessConnectionStringName"];
            if (name == null) name = "lessConnection";
            return name;
        }
    }


    /// <summary>
    /// Name of the connectionstring to use
    /// </summary>
    public static string LessDatabaseJobs
    {
        get
        {
            string name = ConfigurationManager.AppSettings["Data.LessDatabaseJobs"];
            if ( name == null ) name = "DELETE FROM LogEntries WHERE Datediff(day,Created,GETDATE()) > 7 ";
            return name;
        }
    }


    /// <summary>
    /// null= working, false= notPrepared, true = alreadyPrepared
    /// </summary>
    public static bool? Prepared
    {
        get { return _prepared; }
        set { _prepared = value; }
    }

    public static string DatabasePath { get; set; }
    public static string DatabaseLogPath { get; set; }
    public static long SqlSize { get; set; }

    /// <summary>
    /// Gets a new instance of the database manager
    /// </summary>
    /// <returns></returns>
    public static LessDatabase InitializeDatabase()
    {
        LessDatabase database = null;
        try
        {
            string connectionString = GetConnectionString();
            string providerName = GetProviderName();
            if (connectionString != null)
            {
                if (Prepared.HasValue)
                {
                    if (Prepared.GetValueOrDefault())
                    {
                        Log.Debug("Database already prepared");
                        database = new LessDatabase(connectionString, providerName);
                    }
                    else
                    {
                        Log.Debug("Database needs to be prepared");
                        database = PrepareDatabase(connectionString, providerName);
                    }
                }
                else
                {
                    Log.Warning("Database is being prepared", EntryCategories.LessDb);
                }
            }
            Config.CanUseDatabase = true;
        }
        catch (Exception e)
        {
            database.Dispose();
            Log.Fatal(e, EntryCategories.LessDb);
        }
        return database;
    }

    internal static string GetProviderName()
    {
        string providerName = string.Empty;
        ConnectionStringSettings connectionStringSettings =
            ConfigurationManager.ConnectionStrings[LessConnectionStringName];
        if (connectionStringSettings != null)
        {
            providerName = connectionStringSettings.ProviderName;
        }
        return providerName;
    }

    internal static string GetConnectionString()
    {
        string connectionString = string.Empty;
        ConnectionStringSettings connectionStringSettings =
            ConfigurationManager.ConnectionStrings[LessConnectionStringName];
        if (connectionStringSettings != null)
        {
            connectionString = connectionStringSettings.ConnectionString;
            if (connectionString != null)
            {
                if (GetProviderName().Contains("SqlServerCe"))
                {
                    connectionString = string.Format(connectionString, Config.WorkDirectory);
                    string initStr = "Source=";
                    int init = connectionString.IndexOf(initStr);
                    string endStr = ".sdf";
                    int end = connectionString.IndexOf(endStr);
                    DatabasePath = connectionString.Substring(init + initStr.Length,
                                                              end - init - initStr.Length + endStr.Length);
                    DatabaseLogPath = DatabasePath.Replace(".sdf", ".log");
                }
                else
                {
                    DatabasePath = null;
                    DatabaseLogPath = null;
                }
            }
        }
        return connectionString;
    }

    /// <summary>
    /// Verify or create, and update the database
    /// </summary>
    /// <param name="connectionString"></param>
    /// <param name="providerName"></param>
    private static LessDatabase PrepareDatabase(string connectionString, string providerName)
    {
        Prepared = null; // To avoid concurrent calls to this method
        LessDatabase database = null;
        try
        {
            if (VerifySqlCeDatabase(connectionString))
            {
                database = new LessDatabase(connectionString, providerName);
                if (UpdateSchema(database))
                {
                    if (DoDatabaseJobs(database))
                    {
                        Prepared = true;
                    }
                }
            }
            else
            {
                Prepared = false;
            }
        }
        catch (Exception e)
        {
            database.Dispose();
            Log.Fatal(e, EntryCategories.LessDb);
            Prepared = false;
        }
        return database;
    }

    private static bool VerifySqlCeDatabase(string connectionString)
    {
        if (!GetProviderName().Contains("SqlServerCe")) return true;
        bool verified = false;
        SqlCeEngine engine = null;
        try
        {
            engine = new SqlCeEngine(connectionString);
            if (!engine.Verify(VerifyOption.Default))
            {
                Log.Warning("SqlCe DataBase Not Verified or does not exists!!", EntryCategories.LessDb);
                try
                {
                    engine.CreateDatabase();
                    verified = true;
                }
                catch (Exception e)
                {
                    verified = false;
                    // TODO: check if database exists but the file is corrupted
                    Log.Fatal(e, EntryCategories.LessDb);
                }
                Log.Info("SqlCe DataBase created!!", EntryCategories.LessDb);
            }
            else
            {
                verified = true;
                Log.Debug("SqlCe DataBase verified!!", EntryCategories.LessDb);
            }
            if ( File.Exists(DatabasePath) )  SqlSize = new FileInfo(DatabasePath).Length;
            if ( File.Exists(DatabaseLogPath) ) SqlSize += new FileInfo(DatabaseLogPath).Length;
            if (Status.ApplicationStatus != null)
            {
                Status.ApplicationStatus.SqlSize = SqlSize;
                Status.SaveStatus();
            }
        }
        catch (Exception e)
        {
            Log.Fatal(e, EntryCategories.LessDb);
        }
        finally
        {
            if (engine != null)
            {
                engine.Dispose();
            }
        }
        return verified;
    }

    private static bool UpdateSchema(LessDatabase database)
    {
        try
        {
            IEnumerable<string> scriptFiles = GetSQLUpdateSchemas();
            foreach ( string script in scriptFiles )
            {
                if ( Status.ScriptNotExecuted(script) )
                {
                    string[] commands = script.Split(';');
                    if ( ExecuteCommands(database, commands) )
                    {
                        Status.AddExecutedScript(script);
                        Log.Info("Schema updated!!", EntryCategories.LessDb);
                    }
                    else
                    {
                        return false; // Stop processing scripts
                    }
                }
            }
            return true;
        }
        catch (Exception e)
        {
            Log.Fatal(e, EntryCategories.LessDb);
            return false;
        }
    }

    private static bool DoDatabaseJobs(LessDatabase database)
    {
        try
        {
            string databaseJobs = LessDatabaseJobs;
            if (databaseJobs.HasValue())
            {
                string[] commands = databaseJobs.Split(';');
                ExecuteCommands(database, commands);
                Log.Info("DataBase Jobs Done!!", EntryCategories.LessDb);
            }
            return true;
        }
        catch (Exception e)
        {
            Log.Error(e, EntryCategories.LessDb);
            return false;
        }
    }

    private static IEnumerable<string> GetSQLUpdateSchemas()
    {
        var scripts = new List<string>();
        string schema0 = @"CREATE TABLE Users (Id bigint IDENTITY(1,1) PRIMARY KEY, UserName	nvarchar(100)	NOT NULL,Email	nvarchar(100)	NOT NULL,Password	nvarchar(20)	NOT NULL,PasswordQuestion	nvarchar(100)	NULL,PasswordAnswer	nvarchar(100)	NULL,Comment	nvarchar(4000)	NULL,IsApproved	bit	NOT NULL,IsLockedOut		bit	NOT NULL,CreationDate	datetime	NOT NULL,LastLoginDate	datetime	NULL,LastActivityDate	datetime	NULL,LastPasswordChangedDate	datetime	NOT NULL,LastLockoutDate	datetime	NOT NULL);
CREATE TABLE Roles (Id bigint IDENTITY(1,1) PRIMARY KEY,Name	nvarchar(100)	NOT NULL );
CREATE TABLE UsersRoles (Id bigint IDENTITY(1,1) PRIMARY KEY,UserId bigint NOT NULL,RoleId bigint NOT NULL);
CREATE TABLE LogEntries (  Id bigint IDENTITY(1,1) PRIMARY KEY,  Created datetime,  Severity tinyint,  Category tinyint,  Source nvarchar(4000),  Message nvarchar(4000),  SystemData nvarchar(4000),  UserName nvarchar(20));
CREATE TABLE ConfigSettings (  Id bigint IDENTITY(1,1) PRIMARY KEY,  Code nvarchar(100),  Value nvarchar(4000) ); ";
        scripts.Add(schema0);
        string schema1 = @"ALTER TABLE LogEntries ADD ProcessId bigint,MetricName nvarchar(20),MetricValue nvarchar(4000) ";
        scripts.Add(schema1);
        string schema2 = @"ALTER TABLE ConfigSettings ADD Culture nvarchar(10) ";
        scripts.Add(schema2);
        string schema3 = @"ALTER TABLE Users ADD FailedPasswordAttemptCount Integer, FailedPasswordAttemptWindowStart Datetime, FailedPasswordAnswerAttemptCount Integer, FailedPasswordAnswerAttemptWindowStart DateTime, Identifier nvarchar(36) ;";
        scripts.Add(schema3);
        string schema4 = @"ALTER TABLE Users ADD IdentifierExpiry DateTime ;";
        scripts.Add(schema4);
        return scripts;
    }

   

    private static bool ExecuteCommands(LessDatabase database, IEnumerable<string> commands)
    {
        foreach (string command in commands)
        {
            if (command.HasValue())
            {
                try
                {
                    
                    database.Execute(command);
                    Log.Info("OK: " + command, EntryCategories.LessDb);
                }
                catch (Exception e)
                {
                    Log.Fatal(e, EntryCategories.LessDb);
                    return false;
                }
            }
        }
        return true;
    }

    
}